const execSQL = require('./mysql-conn')

async function findAllStudent () {
  const sql = `select * from student left join dept on student.sdept=dept.dno order by sno`
  const result = await execSQL(sql)

  if (result.length === 0) {
    return {code: 201, msg: '未获取到学生信息'}
  } else {
    return {code: 200, msg: '获取学生列表成功', data: result}
  }
}

async function findStudentById (id) {
  const sql = 'select * from student where sno=?'
  const result = await execSQL(sql, id)

  if (result.length === 0) {
    return {code: 201, msg: '未获取到学生信息'}
  } else {
    return {code: 200, msg: '获取学生信息成功', data: result[0]}
  }
}


async function addStudent (obj) {
  obj.stime = new Date();
  sql = 'insert into student set ?'
  const result = await execSQL(sql, obj)

  if (result.affectedRows === 1) {
    const {data} = await findAllStudent()
    return {code: 200, msg: '添加新学生成功', data: data}
  } else {
    return {code: 201, msg: '添加新学生失败'}
  }

}

async function deleteStudentById (id) {
  const sql = 'delete from student where sno=?'
  const result = await execSQL(sql, id)

  if (result.affectedRows === 1) {
    const {data} = await findAllStudent();
    return {code: 200, msg: '删除学生成功', data: data}
  } else {
    return {code: 201, msg: '删除学生失败'}
  }
}

async function modifyStudent (obj) {
  sql = 'update student set ? where sno=?'
  const result = await execSQL(sql, [obj, obj.sno])
  
  if (result.affectedRows === 1) {
    const {data} = await findAllStudent()
    return {code: 200, msg: '修改学生成功', data: data}
  } else {
    return {code: 202, msg: '修改学生失败'}
  }
  
}

module.exports = {
  findAllStudent,
  findStudentById,
  addStudent,
  deleteStudentById,
  modifyStudent
}